﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Transactions;
using MISInterface;

namespace InsideDL.MISData
{
    public class DataARTM95_ABCDE:InsideDL.MISData.DataMatrix,iUpload
    {

        protected override void check(string p_ym)
        {
            string sql = @"
SELECT COUNT(*) AS CNT FROM ARTM95_ABCDE WHERE ymd = @p_ym
";
            System.Data.Common.DbCommand cmd = getCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(getParameter("p_ym", DbType.String, p_ym
                , cmd.CreateParameter()));
            System.Data.DataTable dt = getSQLData(cmd);
            if (!dt.Rows[0]["cnt"].ToString().Equals("0"))
            {
                sql = "DELETE FROM ARTM95_ABCDE WHERE ymd=@p_ym";
                cmd = getCommand();
                cmd.Parameters.Add(getParameter("p_ym", DbType.Date, p_ym
                    , cmd.CreateParameter()));
                cmd.CommandText = sql;
                executeSQL(cmd);
            }
        }

        #region iUpload Members

        /// <summary>
        /// YMD ,分行別 ,幣別 ,大版塊 ,小版塊 ,科目 ,BAL ,發生數
        /// YMD ,分行別 ,幣別 ,大版塊 ,小版塊 ,科目 ,BAL ,月積數均額
        /// </summary>
        /// <param name="p_para"></param>
        public void Upload(object[] p_para)
        {
            string sql, path,ymd;
            object artmAB, artmCDE;
            string[] list, months,  paraNames;

            //要取得ARTM95_ABCDE五個檔案   
            list = p_para[1] as string[];

            paraNames = new[] { "branch", "cur", "big", "small", "acc_no"
                , "bal", "ymd","bal_2" };
            sql = @"
INSERT INTO ARTM95_ABCDE(branch,cur,big,small,acc_no,bal,ymd,bal_2) 
VALUES(@branch, @cur, @big, @small, @acc_no, @bal, @ymd,@bal_2)";
            path = Convert.ToString(p_para[0]);
            ymd = Convert.ToString(p_para[2]);
            check(ymd);
            months = p_para[3]as string[];
            artmAB = getARTMABData(months, path
                , new[] {list[0].ToString(),list[1].ToString() });
            artmCDE = getARTMCDEData(months[0], path
                , new[] { list[2].ToString(), list[3].ToString()
                    , list[4].ToString() }
                , p_para[4].ToString());
            List<string> lstAB = recordsetToLine((ADODB.Recordset)artmAB,ymd);
            List<string> lstCDE = recordsetToLine((ADODB.Recordset)artmCDE,ymd);
            char[] splitMark = new[] { ',' };
            DbType[] dbTypes = new[]{DbType.String,DbType.String,DbType.String
                ,DbType.String,DbType.String,DbType.String,DbType.String
                ,DbType.String};
            lines = lstAB.ToArray();
            base.upLoad(sql, paraNames, splitMark, dbTypes);
            lines = lstCDE.ToArray();
            base.upLoad(sql, paraNames, splitMark, dbTypes);               
        }

        #endregion

        protected List<string> recordsetToLine(ADODB.Recordset p_ado,string p_ymd)
        {
            //p_ado.Move(0,Type.Missing);
            p_ado.MoveFirst();
            List<string> tmpLst = new List<string>();
            for (int i = 0; i < p_ado.RecordCount; i++)
            {
                //branch,cur,big,small,acc_no,bal,ymd,bal_2
                tmpLst.Add(string.Format("{0},{1},{2},{3},{4},{5},{6},{7}"
                    , p_ado.Fields[0].Value, p_ado.Fields[1].Value
                    , p_ado.Fields[2].Value, p_ado.Fields[3].Value
                    , p_ado.Fields[4].Value, p_ado.Fields[5].Value
                    , p_ymd, p_ado.Fields[6].Value
                    ));
                p_ado.MoveNext();
            }
            return tmpLst;
        }

        protected string[] getLine(string p_path, char[] p_mark, string p_ymd)
        {
            string[] tmpLine,split;
            tmpLine = System.IO.File.ReadAllLines(p_path 
                , Encoding.Default );
            int i = 0;
            List<string> tmpLst = new List<string>();
            foreach (string tmp in tmpLine)
            {
                if (i > 0)
                {
                    split = tmp.Split(p_mark);
                    tmpLst.Add(string.Format("{0},{1},{2},{3},{4},{5},{6}"
                    , split[1], split[2], split[3], split[4], split[5],split[6],p_ymd
                        ));                
                }
                i++;
            }
            return tmpLst.ToArray();
        }


        protected object getARTMCDEData(string p_month, string p_path
            , string[] p_files,string p_day)
        {
            string sql;
            sql = string.Format(
@"
SELECT
    分行別 AS [branch]
    ,幣別 AS [cur]     
	,大版塊 AS [big]
    ,小版塊 as [small]
    ,科目 as [accno]
    ,[{4} 月餘額] as [bal] 
    ,ROUND([{4} 月積數]/{3},2) AS [bal2]
FROM {0}
WHERE LEN(大版塊)> 0

UNION ALL

SELECT
分行別 AS [branch]
    ,幣別 AS [cur]     
	,大版塊 AS [big]
    ,小版塊 as [small]
    ,科目 as [accno]
    ,[{4} 月餘額] as [bal] 
    ,ROUND([{4} 月積數]/{3},2) AS [bal2]
FROM {1}
WHERE LEN(大版塊)> 0

UNION ALL

SELECT
分行別 AS [branch]
    ,幣別 AS [cur]     
	,大版塊 AS [big]
    ,小版塊 as [small]
    ,科目 as [accno]
    ,[{4} 月餘額] as [bal] 
    ,ROUND([{4} 月積數]/{3},2) AS [bal2]
FROM {2}
WHERE LEN(大版塊)> 0


", p_files[0],p_files[1],p_files[2],p_day,p_month) ;
            return getCSVDataByRecordSet(sql, p_path);
        }

        protected object getARTMABData(string[] p_months, string p_path
            ,string[] p_files)
        {
            string sql,v1 = "{0} 月餘額]  - [{1} 月餘額";

            if (p_months[1].Equals("12"))
            {
                v1 = string.Format("{0} 月餘額", p_months[0]);
            }
            else
            {
                v1 = string.Format(v1, p_months[0], p_months[1]);
            }
            

            sql = string.Format(@"
SELECT    
    分行別    	
	,幣別 
	,大版塊
	,小版塊
	,科目
    ,[{3} 月餘額] AS [bal]
    ,[{0}] AS 當月發生數
FROM {1} 

UNION ALL

SELECT
    分行別    	
	,幣別 
	,大版塊
	,小版塊
	,科目
    ,[{3} 月餘額] AS [bal]
    ,[{0}] AS 當月發生數
FROM {2} AS A
", v1,p_files[0],p_files[1],p_months[0]);

            return getCSVDataByRecordSet(sql, p_path);
        }
    }
}
